﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using MxWeiXinPF.DBUtility;
using System.Data;

namespace MxWeiXinPF.DAL
{
    public partial class user_grade
    {
        private string databaseprefix;

        public user_grade(string _databaseprefix)
        {
            this.databaseprefix = _databaseprefix;
        }

        public int Add(MxWeiXinPF.Model.user_grade model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into " + this.databaseprefix + "grade(");
            builder.Append("grade,rate,ordernumber,wid,shopid,company)");
            builder.Append(" values (");
            builder.Append("@grade,@rate,@ordernumber,@wid,@shopid,@company)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@grade", SqlDbType.VarChar, 50), new SqlParameter("@rate", SqlDbType.Decimal, 5), new SqlParameter("@ordernumber", SqlDbType.Int), new SqlParameter("@wid", SqlDbType.Int), new SqlParameter("@company", SqlDbType.Decimal, 5), new SqlParameter("@shopid", SqlDbType.Int) };
            cmdParms[0].Value = model.grade;
            cmdParms[1].Value = model.rate;
            cmdParms[2].Value = model.ordernumber;
            cmdParms[3].Value = model.wid;
            cmdParms[4].Value = model.company;
            cmdParms[5].Value = model.shopid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Delete(int id)
        {
            List<CommandInfo> cmdList = new List<CommandInfo>();
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from " + this.databaseprefix + "grade ");
            builder.Append(" where id=@id");
            SqlParameter[] para = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            para[0].Value = id;
            CommandInfo item = new CommandInfo(builder.ToString(), para);
            cmdList.Add(item);
            return (DbHelperSQL.ExecuteSqlTran(cmdList) > 0);
        }

        public bool Exists(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from " + this.databaseprefix + "grade");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public bool ExistsOrderNumber(int shopid, int wid, string ordernumber)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from " + this.databaseprefix + "grade");
            builder.Append(" where ordernumber=@ordernumber ");
            builder.Append(" and shopid=@shopid and wid=@wid ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@shopid", SqlDbType.Int), new SqlParameter("@ordernumber", SqlDbType.NVarChar, 20), new SqlParameter("@wid", SqlDbType.Int) };
            cmdParms[0].Value = shopid;
            cmdParms[1].Value = ordernumber;
            cmdParms[2].Value = wid;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append("  * ");
            builder.Append(" FROM " + this.databaseprefix + "grade ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public MxWeiXinPF.Model.user_grade GetModel(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 * from " + this.databaseprefix + "grade ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            MxWeiXinPF.Model.user_grade _grade = new MxWeiXinPF.Model.user_grade();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count > 0)
            {
                if ((set.Tables[0].Rows[0]["id"] != null) && (set.Tables[0].Rows[0]["id"].ToString() != ""))
                {
                    _grade.id = int.Parse(set.Tables[0].Rows[0]["id"].ToString());
                }
                if ((set.Tables[0].Rows[0]["grade"] != null) && (set.Tables[0].Rows[0]["grade"].ToString() != ""))
                {
                    _grade.grade = set.Tables[0].Rows[0]["grade"].ToString();
                }
                if ((set.Tables[0].Rows[0]["rate"] != null) && (set.Tables[0].Rows[0]["rate"].ToString() != ""))
                {
                    _grade.rate = decimal.Parse(set.Tables[0].Rows[0]["rate"].ToString());
                }
                if ((set.Tables[0].Rows[0]["ordernumber"] != null) && (set.Tables[0].Rows[0]["ordernumber"].ToString() != ""))
                {
                    _grade.ordernumber = int.Parse(set.Tables[0].Rows[0]["ordernumber"].ToString());
                }
                if ((set.Tables[0].Rows[0]["wid"] != null) && (set.Tables[0].Rows[0]["wid"].ToString() != ""))
                {
                    _grade.wid = int.Parse(set.Tables[0].Rows[0]["wid"].ToString());
                }
                if ((set.Tables[0].Rows[0]["shopid"] != null) && (set.Tables[0].Rows[0]["shopid"].ToString() != ""))
                {
                    _grade.shopid = int.Parse(set.Tables[0].Rows[0]["shopid"].ToString());
                }
                if ((set.Tables[0].Rows[0]["company"] != null) && (set.Tables[0].Rows[0]["company"].ToString() != ""))
                {
                    _grade.company = set.Tables[0].Rows[0]["company"].ToString();
                }
                return _grade;
            }
            return null;
        }

        public string GetTitle(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select top 1 grade from " + this.databaseprefix + "grade");
            builder.Append(" where id=" + id);
            string str = Convert.ToString(DbHelperSQL.GetSingle(builder.ToString()));
            if (string.IsNullOrEmpty(str))
            {
                return "";
            }
            return str;
        }

        public bool Update(MxWeiXinPF.Model.user_grade model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + this.databaseprefix + "grade set ");
            builder.Append("grade=@grade,");
            builder.Append("rate=@rate,");
            builder.Append("ordernumber=@ordernumber,");
            builder.Append("company=@company,");
            builder.Append("shopid=@shopid");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@grade", SqlDbType.VarChar, 50), new SqlParameter("@rate", SqlDbType.Decimal, 5), new SqlParameter("@id", SqlDbType.Int, 4), new SqlParameter("@ordernumber", SqlDbType.Int, 4), new SqlParameter("@company", SqlDbType.Decimal, 5), new SqlParameter("@shopid", SqlDbType.Int) };
            cmdParms[0].Value = model.grade;
            cmdParms[1].Value = model.rate;
            cmdParms[2].Value = model.id;
            cmdParms[3].Value = model.ordernumber;
            cmdParms[4].Value = model.company;
            cmdParms[5].Value = model.shopid;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}
